package cn.com.ieds.dao;

import cn.com.ieds.util.DbUtil;
import com.sun.deploy.net.HttpResponse;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDao implements IUserDao {
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet rs;
    private String sql;

    @Override
    // 登录之后把用户信息保存起来
    public void loginAndSaveInfo(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        String account = request.getParameter("account");
        String password = request.getParameter("password");
        sql = "select user_id,user_name,user_password from tbl_user where user_account = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            rs = preparedStatement.executeQuery();
            if (rs.next()) {
                if (password.equals(rs.getString("user_password"))) {
                    Map<String, Object> json = new HashMap<>();
                    json.put("user_id", rs.getInt("user_id"));
                    json.put("user_name", rs.getString("user_name"));
                    response.getWriter().write(JSONObject.fromObject(json).toString());
                }
            } else {
                response.getWriter().write("false");
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 获取全部的账号信息以及角色名称
    public void getAllUserInfoAndRole(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        List<Object> json = new ArrayList<>();
        Map<String,Object> info;
        sql = "select tbl_user.user_id,user_account,user_name,role_name from tbl_user,tbl_role,user_re_role " +
                "where tbl_user.user_id = user_re_role.user_id and tbl_role.role_id = user_re_role.role_id";
        try {
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while(rs.next()){
                info = new HashMap<>();
                info.put("user_id",rs.getInt("user_id"));
                info.put("user_account",rs.getString("user_account"));
                info.put("user_name",rs.getString("user_name"));
                info.put("role_name",rs.getString("role_name"));
                json.add(info);
            }
            response.getWriter().write(JSONArray.fromObject(json).toString());
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 新建账号
    public void addUser(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        String account = request.getParameter("account");
        String name = request.getParameter("name");
        String password = request.getParameter("password");
        int roleId = Integer.parseInt(request.getParameter("roleId"));
        int userId;
        try {
            connection.setAutoCommit(false);
            sql = "insert into tbl_user values(null,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,account);
            preparedStatement.setString(3,password);
            preparedStatement.execute();

            sql = "select user_id from tbl_user where user_account = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,account);
            rs = preparedStatement.executeQuery();
            if(rs.next()){
                userId = rs.getInt("user_id");
                sql = "insert into user_re_role values(null,?,?)";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,userId);
                preparedStatement.setInt(2,roleId);
                preparedStatement.execute();
            }
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            e.printStackTrace();
        } finally {
            connection.close();
        }

    }

    // 删除账号
    public void delUser(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        int user_id = Integer.parseInt(request.getParameter("user_id"));
        try {
            connection.setAutoCommit(false);
            sql = "delete from tbl_user where user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,user_id);
            preparedStatement.execute();
            sql = "delete from user_re_role where user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,user_id);
            preparedStatement.execute();
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 修改账号
    public void updateUser(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        int userId = Integer.parseInt(request.getParameter("user_id"));
        String name = request.getParameter("name");
        String password = request.getParameter("password");
        int roleId = Integer.parseInt(request.getParameter("roleId"));
        try {
            connection.setAutoCommit(false);
            sql = "update tbl_user set user_name = ?,user_password = ? where user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,password);
            preparedStatement.setInt(3,userId);
            preparedStatement.execute();

            sql = "update user_re_role set role_id = ? where user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,roleId);
            preparedStatement.setInt(2,userId);
            preparedStatement.execute();
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 根据角色查询拥有此角色身份的账号
    public void getUserByRole(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        sql = "select user_account,user_name from tbl_user,user_re_role where tbl_user.user_id = user_re_role.user_id and role_id = ?";
        int roleId = Integer.parseInt(request.getParameter("role_id"));
        List<Object> json = new ArrayList<>();
        Map<String,Object> info;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,roleId);
            rs = preparedStatement.executeQuery();
            while(rs.next()){
                info = new HashMap<>();
                info.put("user_account",rs.getString("user_account"));
                info.put("user_name",rs.getString("user_name"));
                json.add(info);
            }
            response.getWriter().write(JSONArray.fromObject(json).toString());
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }


    }
}
